"""
简化的数据库服务层 - 只处理股票恐贪数据
"""
import logging
from datetime import date, timedelta
from typing import List, Dict, Any
import pandas as pd

from .connection import db_manager
from .models import StockFearGreed, StockKline, StockProphecyModel, StockBasicInfo

logger = logging.getLogger(__name__)

class DatabaseService:
    """简化的数据库服务类 - 只处理股票恐贪数据"""
    
    def __init__(self):
        self.db = db_manager
    
    async def get_stock_fear_greed(self, symbol: str, days: int = 365) -> List[StockFearGreed]:
        """获取股票恐贪历史数据"""
        try:
            end_date = date.today()
            start_date = end_date - timedelta(days=days)
            
            with self.db.get_cursor() as cursor:
                cursor.execute("""
                    SELECT symbol, date, open_price, high_price, low_price, close_price, volume,
                           fear_greed_index, sentiment, investment_advice, rsi, volatility,
                           volume_ratio, put_call_ratio, created_at, updated_at
                    FROM stock_fear_greed 
                    WHERE symbol = %s AND date >= %s AND date <= %s
                    ORDER BY date ASC
                """, (symbol, start_date, end_date))
                
                results = cursor.fetchall()
                if not results:
                    logger.debug(f"数据库中未找到 {symbol} 的恐贪数据")
                    return []
                
                # 转换为StockFearGreed对象
                fear_greed_list = []
                for row in results:
                    try:
                        fear_greed_list.append(StockFearGreed(
                            symbol=row['symbol'],
                            date=row['date'],
                            open_price=row['open_price'],
                            high_price=row['high_price'],
                            low_price=row['low_price'],
                            close_price=row['close_price'],
                            volume=row['volume'],
                            fear_greed_index=row['fear_greed_index'],
                            sentiment=row['sentiment'],
                            investment_advice=row['investment_advice'],
                            rsi=row['rsi'],
                            volatility=row['volatility'],
                            volume_ratio=row['volume_ratio'],
                            put_call_ratio=row['put_call_ratio'],
                            created_at=row.get('created_at'),
                            updated_at=row.get('updated_at')
                        ))
                    except Exception as e:
                        logger.error(f"转换数据行失败: {e}, 数据: {row}")
                        continue
                
                logger.info(f"从数据库获取 {symbol} 恐贪数据成功，共 {len(fear_greed_list)} 条记录")
                return fear_greed_list
                
        except Exception as e:
            logger.error(f"获取股票恐贪数据失败 {symbol}: {e}")
            return []
    
    async def save_stock_fear_greed(self, data_list: List[StockFearGreed]) -> int:
        """批量保存股票恐贪数据"""
        try:
            if not data_list:
                return 0
            
            with self.db.get_cursor() as cursor:
                insert_data = []
                for data in data_list:
                    insert_data.append({
                        'symbol': data.symbol,
                        'date': data.date,
                        'open_price': data.open_price,
                        'high_price': data.high_price,
                        'low_price': data.low_price,
                        'close_price': data.close_price,
                        'volume': data.volume,
                        'fear_greed_index': data.fear_greed_index,
                        'sentiment': data.sentiment,
                        'investment_advice': data.investment_advice,
                        'rsi': data.rsi,
                        'volatility': data.volatility,
                        'volume_ratio': data.volume_ratio,
                        'put_call_ratio': data.put_call_ratio
                    })
                
                # 使用REPLACE INTO来确保数据被正确插入或更新
                cursor.executemany("""
                    REPLACE INTO stock_fear_greed 
                    (symbol, date, open_price, high_price, low_price, close_price, volume,
                     fear_greed_index, sentiment, investment_advice, rsi, volatility,
                     volume_ratio, put_call_ratio)
                    VALUES (%(symbol)s, %(date)s, %(open_price)s, %(high_price)s, 
                           %(low_price)s, %(close_price)s, %(volume)s,
                           %(fear_greed_index)s, %(sentiment)s, %(investment_advice)s,
                           %(rsi)s, %(volatility)s, %(volume_ratio)s, %(put_call_ratio)s)
                """, insert_data)
                
                saved_count = cursor.rowcount
                logger.info(f"保存股票恐贪数据成功，共 {saved_count} 条记录")
                return saved_count
                
        except Exception as e:
            logger.error(f"保存股票恐贪数据失败: {e}")
            return 0
    
    async def get_stock_data_as_dataframe(self, symbol: str, days: int = 365) -> pd.DataFrame:
        """获取股票原始数据并转换为DataFrame (from combined table)"""
        history = await self.get_stock_fear_greed(symbol, days)
        if not history:
            return pd.DataFrame()
        
        data = []
        for item in history:
            data.append({
                'date': item.date,
                'Open': float(item.open_price),
                'High': float(item.high_price),
                'Low': float(item.low_price),
                'Close': float(item.close_price),
                'Volume': int(item.volume)
            })
        
        df = pd.DataFrame(data)
        df.set_index('date', inplace=True)
        # 确保索引是DatetimeIndex
        if not isinstance(df.index, pd.DatetimeIndex):
            df.index = pd.to_datetime(df.index)
        return df

    # ==================== 股票K线数据表方法 ====================
    
    async def get_stock_kline(self, symbol: str, days: int = 365, data_source: str = 'akshare') -> List[StockKline]:
        """获取股票K线历史数据"""
        try:
            end_date = date.today()
            start_date = end_date - timedelta(days=days)
            
            with self.db.get_cursor() as cursor:
                cursor.execute("""
                    SELECT symbol, date, open_price, high_price, low_price, close_price, volume,
                           data_source, market_type, created_at, updated_at
                    FROM stock_kline 
                    WHERE symbol = %s AND data_source = %s AND date >= %s AND date <= %s
                    ORDER BY date ASC
                """, (symbol, data_source, start_date, end_date))
                
                results = cursor.fetchall()
                if not results:
                    logger.debug(f"数据库中未找到 {symbol} 的K线数据")
                    return []
                
                kline_list = []
                for row in results:
                    try:
                        kline_list.append(StockKline(
                            symbol=row['symbol'],
                            date=row['date'],
                            open_price=row['open_price'],
                            high_price=row['high_price'],
                            low_price=row['low_price'],
                            close_price=row['close_price'],
                            volume=row['volume'],
                            data_source=row['data_source'],
                            market_type=row['market_type'],
                            created_at=row.get('created_at'),
                            updated_at=row.get('updated_at')
                        ))
                    except Exception as e:
                        logger.error(f"转换K线数据行失败: {e}, 数据: {row}")
                        continue
                
                logger.info(f"从数据库获取 {symbol} K线数据成功，共 {len(kline_list)} 条记录")
                return kline_list
                
        except Exception as e:
            logger.error(f"获取股票K线数据失败 {symbol}: {e}")
            return []
    
    async def save_stock_kline(self, data_list: List[StockKline]) -> int:
        """批量保存股票K线数据"""
        try:
            if not data_list:
                return 0
            
            with self.db.get_cursor() as cursor:
                insert_data = []
                for data in data_list:
                    insert_data.append({
                        'symbol': data.symbol,
                        'date': data.date,
                        'open_price': data.open_price,
                        'high_price': data.high_price,
                        'low_price': data.low_price,
                        'close_price': data.close_price,
                        'volume': data.volume,
                        'data_source': data.data_source,
                        'market_type': data.market_type
                    })
                
                cursor.executemany("""
                    INSERT INTO stock_kline 
                    (symbol, date, open_price, high_price, low_price, close_price, volume,
                     data_source, market_type)
                    VALUES (%(symbol)s, %(date)s, %(open_price)s, %(high_price)s, 
                           %(low_price)s, %(close_price)s, %(volume)s,
                           %(data_source)s, %(market_type)s)
                    AS new_data
                    ON DUPLICATE KEY UPDATE
                    open_price = new_data.open_price,
                    high_price = new_data.high_price,
                    low_price = new_data.low_price,
                    close_price = new_data.close_price,
                    volume = new_data.volume,
                    market_type = new_data.market_type,
                    updated_at = CURRENT_TIMESTAMP
                """, insert_data)
                
                saved_count = cursor.rowcount
                logger.info(f"保存股票K线数据成功，共 {saved_count} 条记录")
                return saved_count
                
        except Exception as e:
            logger.error(f"保存股票K线数据失败: {e}")
            return 0

    # ==================== 股票预测模型结果表方法 ====================
    
    async def get_stock_prophecy_model(self, symbol: str, model_type: str = None, days: int = 30) -> List[StockProphecyModel]:
        """获取股票预测模型结果"""
        try:
            end_date = date.today()
            start_date = end_date - timedelta(days=days)
            
            with self.db.get_cursor() as cursor:
                if model_type:
                    cursor.execute("""
                        SELECT id, symbol, date, model_type, prediction, confidence, features_count,
                               model_accuracy, training_date, feature_importance, macro_indicators,
                               created_at, updated_at
                        FROM stock_prophecy_model 
                        WHERE symbol = %s AND model_type = %s AND date >= %s AND date <= %s
                        ORDER BY date DESC
                    """, (symbol, model_type, start_date, end_date))
                else:
                    cursor.execute("""
                        SELECT id, symbol, date, model_type, prediction, confidence, features_count,
                               model_accuracy, training_date, feature_importance, macro_indicators,
                               created_at, updated_at
                        FROM stock_prophecy_model 
                        WHERE symbol = %s AND date >= %s AND date <= %s
                        ORDER BY date DESC
                    """, (symbol, start_date, end_date))
                
                results = cursor.fetchall()
                if not results:
                    logger.debug(f"数据库中未找到 {symbol} 的预测模型结果")
                    return []
                
                prophecy_list = []
                for row in results:
                    try:
                        prophecy_list.append(StockProphecyModel(
                            id=row['id'],
                            symbol=row['symbol'],
                            date=row['date'],
                            model_type=row['model_type'],
                            prediction=row['prediction'],
                            confidence=row['confidence'],
                            features_count=row['features_count'],
                            model_accuracy=row['model_accuracy'],
                            training_date=row.get('training_date'),
                            feature_importance=row.get('feature_importance'),
                            macro_indicators=row.get('macro_indicators'),
                            created_at=row.get('created_at'),
                            updated_at=row.get('updated_at')
                        ))
                    except Exception as e:
                        logger.error(f"转换预测模型数据行失败: {e}, 数据: {row}")
                        continue
                
                logger.info(f"从数据库获取 {symbol} 预测模型结果成功，共 {len(prophecy_list)} 条记录")
                return prophecy_list
                
        except Exception as e:
            logger.error(f"获取股票预测模型结果失败 {symbol}: {e}")
            return []
    
    async def save_stock_prophecy_model(self, data_list: List[StockProphecyModel]) -> int:
        """批量保存股票预测模型结果"""
        try:
            if not data_list:
                return 0
            
            with self.db.get_cursor() as cursor:
                insert_data = []
                for data in data_list:
                    insert_data.append({
                        'symbol': data.symbol,
                        'date': data.date,
                        'model_type': data.model_type,
                        'prediction': data.prediction,
                        'confidence': data.confidence,
                        'features_count': data.features_count,
                        'model_accuracy': data.model_accuracy,
                        'training_date': data.training_date,
                        'feature_importance': data.feature_importance,
                        'macro_indicators': data.macro_indicators
                    })
                
                cursor.executemany("""
                    INSERT INTO stock_prophecy_model 
                    (symbol, date, model_type, prediction, confidence, features_count,
                     model_accuracy, training_date, feature_importance, macro_indicators)
                    VALUES (%(symbol)s, %(date)s, %(model_type)s, %(prediction)s, %(confidence)s,
                           %(features_count)s, %(model_accuracy)s, %(training_date)s,
                           %(feature_importance)s, %(macro_indicators)s)
                    AS new_data
                    ON DUPLICATE KEY UPDATE
                    prediction = new_data.prediction,
                    confidence = new_data.confidence,
                    features_count = new_data.features_count,
                    model_accuracy = new_data.model_accuracy,
                    training_date = new_data.training_date,
                    feature_importance = new_data.feature_importance,
                    macro_indicators = new_data.macro_indicators,
                    updated_at = CURRENT_TIMESTAMP
                """, insert_data)
                
                saved_count = cursor.rowcount
                logger.info(f"保存股票预测模型结果成功，共 {saved_count} 条记录")
                return saved_count
                
        except Exception as e:
            logger.error(f"保存股票预测模型结果失败: {e}")
            return 0

    # ==================== 股票基础信息表方法 ====================
    
    async def get_stock_basic_info(self, symbol: str = None, market_type: str = None) -> List[StockBasicInfo]:
        """获取股票基础信息"""
        try:
            with self.db.get_cursor() as cursor:
                if symbol:
                    cursor.execute("""
                        SELECT symbol, name, market_type, sector, industry, country, currency,
                               exchange, is_active, custom_category, description,
                               created_at, updated_at
                        FROM stock_basic_info 
                        WHERE symbol = %s
                    """, (symbol,))
                elif market_type:
                    cursor.execute("""
                        SELECT symbol, name, market_type, sector, industry, country, currency,
                               exchange, is_active, custom_category, description,
                               created_at, updated_at
                        FROM stock_basic_info 
                        WHERE market_type = %s AND is_active = 'Y'
                        ORDER BY symbol
                    """, (market_type,))
                else:
                    cursor.execute("""
                        SELECT symbol, name, market_type, sector, industry, country, currency,
                               exchange, is_active, custom_category, description,
                               created_at, updated_at
                        FROM stock_basic_info 
                        WHERE is_active = 'Y'
                        ORDER BY market_type, symbol
                    """)
                
                results = cursor.fetchall()
                if not results:
                    logger.debug(f"数据库中未找到股票基础信息")
                    return []
                
                basic_info_list = []
                for row in results:
                    try:
                        basic_info_list.append(StockBasicInfo(
                            symbol=row['symbol'],
                            name=row['name'],
                            market_type=row['market_type'],
                            sector=row['sector'],
                            industry=row['industry'],
                            country=row['country'],
                            currency=row['currency'],
                            exchange=row['exchange'],
                            is_active=row['is_active'],
                            custom_category=row['custom_category'],
                            description=row.get('description'),
                            created_at=row.get('created_at'),
                            updated_at=row.get('updated_at')
                        ))
                    except Exception as e:
                        logger.error(f"转换基础信息数据行失败: {e}, 数据: {row}")
                        continue
                
                logger.info(f"从数据库获取股票基础信息成功，共 {len(basic_info_list)} 条记录")
                return basic_info_list
                
        except Exception as e:
            logger.error(f"获取股票基础信息失败: {e}")
            return []
    
    async def save_stock_basic_info(self, data_list: List[StockBasicInfo]) -> int:
        """批量保存股票基础信息"""
        try:
            if not data_list:
                return 0
            
            with self.db.get_cursor() as cursor:
                insert_data = []
                for data in data_list:
                    insert_data.append({
                        'symbol': data.symbol,
                        'name': data.name,
                        'market_type': data.market_type,
                        'sector': data.sector,
                        'industry': data.industry,
                        'country': data.country,
                        'currency': data.currency,
                        'exchange': data.exchange,
                        'is_active': data.is_active,
                        'custom_category': data.custom_category,
                        'description': data.description
                    })
                
                cursor.executemany("""
                    INSERT INTO stock_basic_info 
                    (symbol, name, market_type, sector, industry, country, currency,
                     exchange, is_active, custom_category, description)
                    VALUES (%(symbol)s, %(name)s, %(market_type)s, %(sector)s, %(industry)s,
                           %(country)s, %(currency)s, %(exchange)s, %(is_active)s,
                           %(custom_category)s, %(description)s)
                    AS new_data
                    ON DUPLICATE KEY UPDATE
                    name = new_data.name,
                    market_type = new_data.market_type,
                    sector = new_data.sector,
                    industry = new_data.industry,
                    country = new_data.country,
                    currency = new_data.currency,
                    exchange = new_data.exchange,
                    is_active = new_data.is_active,
                    custom_category = new_data.custom_category,
                    description = new_data.description,
                    updated_at = CURRENT_TIMESTAMP
                """, insert_data)
                
                saved_count = cursor.rowcount
                logger.info(f"保存股票基础信息成功，共 {saved_count} 条记录")
                return saved_count
                
        except Exception as e:
            logger.error(f"保存股票基础信息失败: {e}")
            return 0

# 创建全局数据库服务实例
db_service = DatabaseService()
